<?php
namespace Admin\Controller;
use Think\Controller;
class ExcelController extends CommonController {
	
    function index(){
		$this->myArray();
        $this -> display();
    }

	public function exportExcel(){
		$idArr = $_GET['idArr'];
		if(!empty($idArr)){
			$map['id'] = array('in',$idArr);
		}
		$M = M('Webedit');
		$list = $M->where($map)->select();
		$title = array(
            'platform'=>'平台',
			'title'=>'标题',
            'keyword'=>'关键词',
            'url'=>'文章地址',
			'creater'=>'发布人',
			'record'=>'收录情况',
			'ranking'=>'排名',
			'check_status'=>'考核结果',
			'check_time'=>'考核日期',
			'remark'=>'备注'

		);
		//$this->exportDispose($list, $title,date("Ymd-His"));
		$this->down_xls($list, $title,date("Ymd-His"));
	}

	public function importExcel($dbName,$fieldList,$encode='utf-8'){
		//$fieldList数组键为字段名，值和导入EXCEL标题对应
		if(!is_array($fieldList)){
			$fieldList = explode(',',$fieldList);
		}
		$filePath = $this->upFile();
		$returnData = $this->exportPHPExcel ($filePath);

		foreach ($fieldList as $key => $val) {
			$fieldArr[$val] = $key;
		}

		foreach($returnData[1] as $k=>$v){
			if(in_array($v,$fieldList)){
				$newFieldArr[$k] = $fieldArr[$v];
			}
		}

		foreach ( $returnData as $key => $val ){
			foreach ( $newFieldArr as $k => $v ){
				$data [$v] = $val [$k];
			}
			$data ['create_time'] = time();
			$result[] = M($dbName)->add($data);
		}
		$returnNum = count($result);
		return $returnNum;
	}

	public function exportPHPExcel($filename,$encode='utf-8'){
		 import("ORG.PHPExcel.PHPExcel");
		 $objPHPExcel = new PHPExcel();

          $objReader = PHPExcel_IOFactory::createReader('Excel5');

          $objReader->setReadDataOnly(true);

          $objPHPExcel = $objReader->load($filename);

          $objWorksheet = $objPHPExcel->getActiveSheet();
		  $highestRow = $objWorksheet->getHighestRow();
		  $highestColumn = $objWorksheet->getHighestColumn();
		  $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
		$excelData = array();
		for ($row = 1; $row <= $highestRow; $row++){
			for ($col = 0; $col < $highestColumnIndex; $col++) {
                 $excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
           }
		}
		return $excelData;
    }


	//down_xls(二维数组, 设置一排标题,date("Ymd-His"));
	function down_xls($data, $keynames, $name='dataxls') {
		$xls[] = "<html><meta http-equiv=content-type content=\"text/html; charset=UTF-8\"><body><table border='1'>";
		$xls[] = "<tr><th>ID</th><th>" . implode("</th><th>", array_values($keynames)) . '</th></tr>';
		foreach($data As $o) {
			$line = array(++$index);
			foreach($keynames AS $k=>$v) {
				switch($k){
					case 'create_time':$line[] = $o[$k]>0?date('Y-m-d',$o[$k]):'&nbsp;';break;
					case 'update_time':$line[] = $o[$k]>0?date('Y-m-d',$o[$k]):'&nbsp;';break;
					case 'sex':$line[] = $o[$k]==0?'女':'男';break;
					case 'city':$line[] = region($o[$k]);break;
					default:$line[] = $o[$k];
				}
			}
			$xls[] = '<tr><td>&nbsp;' .implode("&nbsp;</td><td>&nbsp;",$line) . '&nbsp;</td></tr>';
		}
		$xls[] = '</table></body></html>';
		$xls = join("\r\n", $xls);
		header('Content-Disposition: attachment; filename="'.$name.'.xls"');
		header('Content-Type: application/vnd.ms-excel');
		header('Cache-Control: max-age=0');
		die(mb_convert_encoding($xls,'UTF-8','UTF-8'));
    }

	public function exportDispose($list,$title,$fileName){
		$fileName = $fileName ? trim($fileName) : date("Y-m-d");
		header("Content-type:application/octet-stream");
		header("Accept-Ranges:bytes");
		header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=".$fileName.".xls");
		header("Pragma: no-cache");
		header("Expires: 0");
		foreach($title as $k=>$v){
				$str .=	iconv("UTF-8", "GB2312", $v)."\t";
		}
		$str .= "\n";
		foreach($list as $key=>$val){
			foreach($title as $k=>$v){
				switch($k){
					case 'create_time':
						$line[] = $o[$k]>0?date('Y-m-d',$o[$k]):'&nbsp;';
						break;
					case 'update_time':
						$line[] = $o[$k]>0?date('Y-m-d',$o[$k]):'&nbsp;';
						break;
					case 'sex':
						$val[$k] = $val[$k]==0 ? '女':'男';
						break;
					default:

				}
				$str .=	iconv("UTF-8", "GB2312", $val[$k])."\t";
			}
			echo "$str\n";
			$str = '';
		}


	}

     /* 导出excel函数*/
    public function push($data,$name='Excel'){
 		import("ORG.PHPExcel.PHPExcel");
        error_reporting(E_ALL);
        date_default_timezone_set('Europe/London');
        $objPHPExcel = new PHPExcel();

        /*以下是一些设置 ，什么作者  标题啊之类的*/
        $objPHPExcel->getProperties()->setCreator("转弯的阳光")
                               ->setLastModifiedBy("转弯的阳光")
                               ->setTitle("数据EXCEL导出")
                               ->setSubject("数据EXCEL导出")
                               ->setDescription("备份数据")
                               ->setKeywords("excel")
                              ->setCategory("result file");
        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
        foreach($data as $k => $v){
			$num=$k+1;
            $objPHPExcel->setActiveSheetIndex(0)
				//Excel的第A列，uid是你查出数组的键值，下面以此类推
                          ->setCellValue('A'.$num, $v['uid'])
                          ->setCellValue('B'.$num, $v['email'])
                          ->setCellValue('C'.$num, $v['password']);
		}

        $objPHPExcel->getActiveSheet()->setTitle('User');
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$name.'.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

	}

	private function upFile(){
		if (empty ( $_FILES ['file'] ['name'] )){
			return $this->errorNum = -8;
		}
		$tmp_file = $_FILES ['file'] ['tmp_name'];
			$file_types = explode ( ".", $_FILES ['file'] ['name'] );
			$file_type = $file_types [count ( $file_types ) - 1];

			 /*判别是不是.xls文件，判别是不是excel文件*/
			if (strtolower ( $file_type ) != "xls"){
				return $this->errorNum = -2;
			}

			/*设置上传路径*/
			$savePath = './Public/Uploads/Excel/';
			if(!file_exists($savePath)){
				if(!@mkdir($savePath,0755)){
					return $this->errorNum = -7;
				 }
			}
			/*以时间来命名上传的文件*/
			$str = date ( 'Ymdhis' );
			$file_name = $str . "." . $file_type;

			 /*是否上传成功*/
			if (! copy ( $tmp_file, $savePath . $file_name )){
				return $this->errorNum = -5;
			 }

		return $savePath.$file_name;
	}

	public function getErrorMsg(){
         $str="上传文件出错：";
         switch ($this->errorNum){
             case -1:
                 $str.="未知错误！";
                 break;
             case -2:
                 $str.="未允许类型！";
                 break;
             case -3:
                 $str.="文件过大！";
                 break;
             case -4:
                 $str.="产生文件名出错！";
                 break;
             case -5:
                 $str.="上传失败！";
                 break;
             case -6:
                 $str.="目录不存在！";
                 break;
             case -7:
                 $str.="建立目录失败！";
                 break;
			case -8:
                 $str.="请选择上传文件！";
                 break;
         }
         return $str;
     }
}